	
		**********************************************************
		** File: cleaning_cpi.do								**
		** Paper: Human Trafficking Indicators: A New Dataset	**
		** Author: Richard Frank								**
		** Date: July 11, 2021									**
		** Task: Cleaning Transparency International			**
 		**********************************************************

	clear
	set more off
	version 16.0
	set seed 1234
 
	
	cd  "~"
	
	** data downloaded from: https://www.transparency.org/en/cpi/ 
	
	*** 1998-2015, 1995-7 are in PDFS ***
	
	** 1998 **
	import excel "CPI 1998.xlsx", sheet("Sheet1") firstrow case(lower) clear
	rename cpi1998score cpi
	rename countryterritory country
	keep cpi country
	gen year=1998
	save cpi_1998.dta, replace
	
	** 1999 **
	import excel "CPI 1999.xlsx", sheet("Sheet1") firstrow case(lower) clear
	rename cpi1999score cpi
	rename countryterritory country
	keep cpi country
	gen year=1999
	save cpi_1999.dta, replace
		
	** 2000 **
	import excel "CPI 2000.xlsx", sheet("Sheet1") firstrow case(lower) clear
	rename cpi2000score cpi
	rename countryterritory country
	keep cpi country
	gen year=2000
	save cpi_2000.dta, replace		
		
	** 2001 **
	import excel "CPI 2001.xlsx", sheet("Sheet1") firstrow case(lower) clear
	rename cpi2001score cpi
	rename countryterritory country
	keep cpi country
	gen year=2001
	save cpi_2001.dta, replace			
		
	** 2002 **
	import excel "CPI 2002.xlsx", sheet("Sheet1") firstrow case(lower) clear
	rename cpi2002score cpi
	rename countryterritory country
	keep cpi country
	gen year=2002
	save cpi_2002.dta, replace		
	
	** 2003 **
	import excel "CPI 2003.xlsx", sheet("Sheet1") firstrow case(lower) clear
	rename cpi2003score cpi
	rename countryterritory country
	keep cpi country
	gen year=2003
	save cpi_2003.dta, replace		
	
	** 2004 **
	import excel "CPI 2004.xlsx", sheet("Sheet1") firstrow case(lower) clear
	rename cpi2004score cpi
	rename countryterritory country
	keep cpi country
	gen year=2004
	save cpi_2004.dta, replace		
	
	** 2005 **
	import excel "CPI 2005.xlsx", sheet("Sheet1") firstrow case(lower) clear
	rename cpi2005score cpi
	rename countryterritory country
	keep cpi country
	gen year=2005
	save cpi_2005.dta, replace		
	
	** 2006 **
	import excel "CPI 2006.xlsx", sheet("Sheet1") firstrow case(lower) clear
	rename cpi2006score cpi
	rename countryterritory country
	keep cpi country
	gen year=2006
	save cpi_2006.dta, replace		
	
	** 2007 **
	import excel "CPI 2007.xlsx", sheet("Sheet1") firstrow case(lower) clear
	rename cpi2007score cpi
	rename countryterritory country
	keep cpi country
	gen year=2007
	save cpi_2007.dta, replace		
	
	** 2008 **
	import excel "CPI 2008.xlsx", sheet("Sheet1") firstrow case(lower) clear
	rename cpi2008score cpi
	rename countryterritory country
	keep cpi country
	gen year=2008
	save cpi_2008.dta, replace		
	
	** 2009 **
	import excel "CPI 2009.xlsx", sheet("Sheet1") firstrow case(lower) clear
	rename cpi2009score cpi
	keep cpi country
	gen year=2009
	save cpi_2009.dta, replace		
	
	** 2010 **
	import excel "CPI 2010.xlsx", sheet("CPI table") cellrange(A4:V182) firstrow clear
	rename cpi2010 cpi
	keep cpi country
	gen year=2010
	save cpi_2010.dta, replace		
	
	** 2011 **
	import excel "CPI 2011.xlsx", sheet("Global") firstrow case(lower) clear
	drop in 1
	rename cpi2011score cpi
	rename countryterritory country
	keep cpi country
	gen year=2011
	save cpi_2011.dta, replace		
								
	** 2012 **
	import excel "CPI 2012.xlsx", sheet("CPI 2012") firstrow case(lower) clear
	drop in 1
	rename cpi2012score cpi
	rename countryterritory country
	keep cpi country
	gen year=2012
	save cpi_2012.dta, replace		
								
	** 2013 **
	import excel "CPI 2013.xlsx", sheet("CPI 2013") firstrow case(lower) clear
	rename cpi2013score cpi
	keep cpi country
	gen year=2013
	save cpi_2013.dta, replace			
	
	** 2014 **
	import excel "CPI 2014.xlsx", sheet("CPI 2014") cellrange(A2:AA178) firstrow case(lower) clear
	rename cpi2014score cpi
	rename countryterritory country	
	keep cpi country
	gen year=2014
	save cpi_2014.dta, replace			
	
	** 2015 **
	import excel "CPI 2015.xlsx", sheet("CPI 2015") cellrange(A2:AA172) firstrow case(lower) clear
	rename cpi2015 cpi
	keep cpi country
	gen year=2015
	save cpi_2015.dta, replace		
	
	** 2016 **
	import excel "CPI 2016.xlsx", sheet("CPI 2016") firstrow case(lower) clear
	rename cpi2016 cpi
	keep cpi country
	gen year=2016
	save cpi_2016.dta, replace	
	
	** 2017 **
	import excel "CPI 2017.xlsx", sheet("CPI 2017") cellrange(A3:I185) firstrow case(lower) clear

	rename cpiscore2017 cpi
	keep cpi country
	gen year=2017
	save cpi_2017.dta, replace		
	
	
	** 2018 **
	import excel "CPI 2018.xlsx", sheet("CPI2018") cellrange(A3:V183) firstrow case(lower) clear

	rename cpiscore2018 cpi
	keep cpi country
	gen year=2018
	save cpi_2018.dta, replace		
	
	append using cpi_2017.dta			
	append using cpi_2016.dta		
	append using cpi_2015.dta	
	append using cpi_2014.dta
	append using cpi_2013.dta	
	append using cpi_2012.dta	
	append using cpi_2011.dta
	append using cpi_2010.dta	
	append using cpi_2009.dta	
	append using cpi_2008.dta	
	append using cpi_2007.dta	
	append using cpi_2006.dta	
	append using cpi_2005.dta	
	append using cpi_2004.dta	
	append using cpi_2003.dta	
	append using cpi_2002.dta	
	append using cpi_2001.dta	
	append using cpi_2000.dta	
	append using cpi_1999.dta	
	append using cpi_1998.dta
	
	
	rename country Country
	label var year "Year"
	label var cpi "Corruption Perception Index (Transparency International)"

	run "cow.do"
	
	drop if ccode==0
	duplicates report ccode year
	sort ccode year
	browse if ccode==ccode[_n-1] & year==year[_n-1]
	drop if Country=="Palestine"
	drop if Country=="Hong Kong"
	drop if Country=="Macau"	
	rename Country country
	order ccode year country cpi
	
	
	replace cpi=cpi/10 if year==2012
	replace cpi=cpi/10 if year==2013	
	replace cpi=cpi/10 if year==2014	
	replace cpi=cpi/10 if year==2015	
	replace cpi=cpi/10 if year==2016	
	replace cpi=cpi/10 if year==2017	
	replace cpi=cpi/10 if year==2018
	
	sort ccode year
	
	save cpi_98_18.dta, replace
